using System;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.InteropServices;

namespace Antu.Core.DataAccess
{
    /// <summary>
    /// SqlServer 通用数据访问类
    /// </summary>
    internal class SqlDataAccess : IDataAccess
    {
        private SqlConnection con;
        private bool isTransaction;
        private bool IsUpdate;
        private SqlDataAdapter sda;
        private string strConnectionString;
        private SqlTransaction transaction;

        /// <summary>
        /// 不使用事务，联接串自动从配置文件取
        /// </summary>
        public SqlDataAccess()
        {
            this.isTransaction = false;
            this.IsUpdate = false;
            this.strConnectionString = "";
            this.isTransaction = false;
        }

        /// <summary>
        /// 使用事务
        /// </summary>
        /// <param name="beginTransaction">true则使用事务</param>
        public SqlDataAccess(bool beginTransaction)
        {
            this.isTransaction = false;
            this.IsUpdate = false;
            this.strConnectionString = "";
            this.isTransaction = beginTransaction;
        }

        /// <summary>
        /// 不使用事务，联接串指定
        /// </summary>
        /// <param name="connectionString">联接串</param>
        public SqlDataAccess(string connectionString)
        {
            this.isTransaction = false;
            this.IsUpdate = false;
            this.strConnectionString = "";
            this.isTransaction = false;
            this.strConnectionString = connectionString;
        }

        /// <summary>
        /// 使用事务
        /// </summary>
        /// <param name="beginTransaction">true则使用事务</param>
        /// <param name="connectionString">联接串</param>
        public SqlDataAccess(bool beginTransaction, string connectionString)
        {
            this.isTransaction = false;
            this.IsUpdate = false;
            this.strConnectionString = "";
            this.isTransaction = beginTransaction;
            this.strConnectionString = connectionString;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            if (this.con != null)
            {
                this.con.Close();
            }
        }

        /// <summary>
        /// 提交或撤消更改并关闭数据库连接
        /// </summary>
        /// <param name="commit">True则提交,否则撤消</param>
        public void Close(bool commit)
        {
            if (this.transaction != null)
            {
                if (commit)
                {
                    this.transaction.Commit();
                }
                else
                {
                    this.transaction.Rollback();
                }
            }
            this.Close();
        }

        /// <summary>
        /// 创建一个SqlCommand对象以此来执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="prams">存储过程所需参数</param>
        /// <returns>返回SqlCommand对象</returns>
        private SqlCommand CreateCommand(string procName, ref IDataParameter[] prams)
        {
            this.Open();
            SqlCommand cmd = new SqlCommand(procName, this.con);
            cmd.CommandType = CommandType.StoredProcedure;
            if ((this.transaction != null) && this.isTransaction)
            {
                cmd.Transaction = this.transaction;
            }
            if (prams != null)
            {
                foreach (IDataParameter parameter in prams)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
            return cmd;
        }

        /// <summary>
        /// 检查对应于条件的数据是否存在
        /// </summary>
        /// <param name="tableName">表名</param> 
        /// <param name="where">条件,包括Where语句,如Where a=2 </param>
        /// <returns></returns>
        public bool DataExists(string tableName, string where)
        {
            SqlDataReader dr;
            string SqlStr = "SELECT * FROM " + tableName + " " + where;
            this.RunSql(SqlStr, out dr);
            return dr.HasRows;
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public void Dispose()
        {
            if (this.con != null)
            {
                this.con.Dispose();
                this.con = null;
            }
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程.返回IDataReader
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>IDataReader</returns>
        public IDataReader GetDataReader(string sql)
        {
            this.Open();
            SqlCommand cmd = new SqlCommand(sql, this.con);
            cmd.CommandType = CommandType.Text;
            if ((this.transaction != null) && this.isTransaction)
            {
                cmd.Transaction = this.transaction;
            }
            return cmd.ExecuteReader(CommandBehavior.SingleRow);
        }

        /// <summary>
        /// 运行SQL语句返回第一行第一个字段的值（字符串形式）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public string GetValue(string sql)
        {
            DataTable dt;
            this.RunSql(sql, out dt);
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0].ToString();
            }
            return "";
        }

        /// <summary>
        /// 打开数据库连接.
        /// </summary>
        private void Open()
        {
            if (this.con == null)
            {
                if (this.strConnectionString == string.Empty)
                {
                    this.con = new SqlConnection(ConnectionText.SqlText);
                }
                else
                {
                    this.con = new SqlConnection(this.strConnectionString);
                }
            }
            if (this.con.State == ConnectionState.Closed)
            {
                if (this.strConnectionString == string.Empty)
                {
                    this.con.ConnectionString = ConnectionText.SqlText;
                }
                else
                {
                    this.con.ConnectionString = this.strConnectionString;
                }
                this.con.Open();
                if (this.isTransaction)
                {
                    this.transaction = this.con.BeginTransaction();
                }
            }
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <returns>返回存储过程返回值</returns>
        public int RunProc(string procName)
        {
            IDataParameter[] prams = null;
            object obj = this.CreateCommand(procName, ref prams).ExecuteScalar().ToString();
            if (!this.isTransaction)
            {
                this.Close();
            }
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            return 0;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="prams">存储过程所需参数</param>
        /// <returns>返回存储过程返回值</returns>
        public int RunProc(string procName, ref IDataParameter[] prams)
        {
            object obj = this.CreateCommand(procName, ref prams).ExecuteScalar();
            if (!this.isTransaction)
            {
                this.Close();
            }
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            return 0;
        }

        /// <summary>
        /// 执行存储过程,将结果以DataSet返回
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="prams">参数数组</param>
        /// <param name="ds">返回的数据集</param>
        public void RunProc(string procName, ref IDataParameter[] prams, out DataSet ds)
        {
            SqlDataAdapter tda = new SqlDataAdapter(this.CreateCommand(procName, ref prams));
            ds = new DataSet();
            tda.Fill(ds);
            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行存储过程,将结果以DataTable返回
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="prams">参数数组</param>
        /// <param name="dt">返回的数据集</param>
        public void RunProc(string procName, ref IDataParameter[] prams, out DataTable dt)
        {
            SqlDataAdapter tda = new SqlDataAdapter(this.CreateCommand(procName, ref prams));
            dt = new DataTable();
            tda.Fill(dt);
            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行存储过程,以DataReader返回
        /// </summary>
        /// <param name="procName">存储过程的名称</param>
        /// <param name="prams">参数数组</param>
        /// <param name="dataReader">返回存储过程返回值</param>
        public void RunProc(string procName, ref IDataParameter[] prams, out SqlDataReader dataReader)
        {
            dataReader = this.CreateCommand(procName, ref prams).ExecuteReader(CommandBehavior.CloseConnection);
            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程
        /// </summary>
        /// <param name="sql">要执行的Sql语句</param>
        public int RunSql(string sql)
        {
            this.Open();
            SqlCommand cmd = new SqlCommand(sql, this.con);
            cmd.CommandType = CommandType.Text;
            if ((this.transaction != null) && this.isTransaction)
            {
                cmd.Transaction = this.transaction;
            }
            object obj = cmd.ExecuteScalar();
            if (!this.isTransaction)
            {
                this.Close();
            }
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            return 0;
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程.以参数dt返回结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="dt">结果集</param>
        /// <returns></returns>
        public void RunSql(string sql, out DataTable dt)
        {
            this.RunSql(sql, out dt, false);
        }

        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="prams">所需参数</param>
        /// <returns>返回存储过程返回值</returns>
        public DataTable RunSql(string sql, ref IDataParameter[] prams)
        {
            SqlCommand cmd = this.CreateCommand(sql, ref prams);
            cmd.CommandType = CommandType.Text;
            this.sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            this.sda.Fill(dt);
            return dt;
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程.以参数ds返回结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="ds">结果集</param>
        /// <returns></returns>
        public void RunSql(string sql, out DataSet ds)
        {
            this.RunSql(sql, out ds, false);
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程.以参数SqlDataReader返回结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="dataReader"></param>
        private void RunSql(string sql, out SqlDataReader dataReader)
        {
            this.Open();
            SqlCommand cmd = new SqlCommand(sql, this.con);
            cmd.CommandType = CommandType.Text;
            if ((this.transaction != null) && this.isTransaction)
            {
                cmd.Transaction = this.transaction;
            }
            dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程.以参数ds返回结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="ds">结果集</param>
        /// <param name="isUpdate">是否启用更新，True时启用</param>
        /// <returns></returns>
        public void RunSql(string sql, out DataSet ds, bool isUpdate)
        {
            this.Open();
            SqlCommand cmd = new SqlCommand(sql, this.con);
            cmd.CommandType = CommandType.Text;
            if ((this.transaction != null) && this.isTransaction)
            {
                cmd.Transaction = this.transaction;
            }
            this.sda = new SqlDataAdapter(cmd);
            if (isUpdate)
            {
                SqlCommandBuilder cBuilder = new SqlCommandBuilder(this.sda);
                this.IsUpdate = isUpdate;
            }
            ds = new DataSet();
            this.sda.Fill(ds);
            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 执行SQL语句,慎用,尽可能使用存储过程.以参数dt返回结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="dt">结果集</param>
        /// <param name="isUpdate">返回的数据集是否支持更新，如果是，则可以调用此类的Update方法进行更新数据集</param>
        /// <returns></returns>
        public void RunSql(string sql, out DataTable dt, bool isUpdate)
        {
            this.Open();
            SqlCommand cmd = new SqlCommand(sql, this.con);
            cmd.CommandType = CommandType.Text;
            if ((this.transaction != null) && this.isTransaction)
            {
                cmd.Transaction = this.transaction;
            }
            this.sda = new SqlDataAdapter(cmd);
            if (isUpdate)
            {
                SqlCommandBuilder cBuilder = new SqlCommandBuilder(this.sda);
                this.IsUpdate = isUpdate;
            }
            dt = new DataTable();
            this.sda.Fill(dt);
            if (!this.isTransaction)
            {
                this.Close();
            }
        }

        /// <summary>
        /// 更新指定的DataSet中的指定表的数据到源数据库中
        /// </summary>
        /// <param name="dt">指定要更新的数据表</param>
        public void Update(DataTable dt)
        {
            if (!this.IsUpdate)
            {
                throw new Exception("Update方法必须要先执行了RunSQL方法并设了参数isUpdate＝true才有效");
            }
            this.sda.Update(dt);
        }

        /// <summary>
        /// 更新指定的DataSet中的指定表的数据到源数据库中
        /// </summary>
        /// <param name="ds">指定要更新的数据集</param>
        /// <param name="TableName">指定是数据集中的那个表</param>
        public void Update(DataSet ds, string TableName)
        {
            if (!this.IsUpdate)
            {
                throw new Exception("Update方法必须要先执行了RunSQL方法并设了参数isUpdate＝true才有效");
            }
            this.sda.Update(ds, TableName);
        }

        /// <summary>
        /// 联接串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return this.strConnectionString;
            }
            set
            {
                this.Close();
                this.strConnectionString = value;
            }
        }
    }
}

